﻿IF OBJECT_ID('[core].[fn_GetDefaultLanguageID]') IS NOT NULL
BEGIN
	DROP FUNCTION [core].[fn_GetDefaultLanguageID];
END
GO

CREATE FUNCTION [core].[fn_GetDefaultLanguageID]()
RETURNS INT
AS
BEGIN
	RETURN 1031;
END
GO

ALTER PROCEDURE [core].[AddVersion]
	@VersionNumber VARCHAR(25),
	@SetAsCurrent BIT = 1,
	@RolloutDate DATE = GETDATE
AS
BEGIN
	DECLARE	@v_VersionNumber VARCHAR(25) = @VersionNumber,
			@v_SetAsCurrent BIT = @SetAsCurrent,
			@v_RolloutDate DATE = @RolloutDate,
			@c_Source NVARCHAR(256) = 'core.AddVersion'
			
	IF @v_RolloutDate IS NULL
	BEGIN
		SET @v_RolloutDate = GETDATE()
	END
	
	IF @v_SetAsCurrent IS NULL
	BEGIN
		SET @v_SetAsCurrent = 1
	END

	BEGIN TRANSACTION
		INSERT INTO [core].[Version](VersionNumber, RolloutDate, IsCurrent, CreatedBy, CreatedAt, CreatedFromSource)
		SELECT @v_VersionNumber, @v_RolloutDate, @v_SetAsCurrent, SUSER_SNAME(), GETDATE(), @c_Source
		
		IF @v_SetAsCurrent = 1
		BEGIN
			
			/* Set the old Version that is current to the not current version */
			UPDATE [core].[Version]
				SET IsCurrent = 0,
					ModifiedAt = GETDATE(),
					ModifiedBy = SUSER_SNAME(),
					ModifiedFromSource = @c_Source
				WHERE IsCurrent = 1
				AND VersionNumber <> @v_VersionNumber
			
			/* Set the new current version */
			UPDATE [core].[Version]
				SET IsCurrent = 1
				WHERE VersionNumber = @v_VersionNumber
			
		END
	
	COMMIT TRANSACTION
END
GO

IF OBJECT_ID('[core].[fn_GetErrorTextByErrorCode]') IS NOT NULL
BEGIN
	DROP FUNCTION [core].[fn_GetErrorTextByErrorCode];
END
GO

CREATE FUNCTION [core].[fn_GetErrorTextByErrorCode]
(
	@ErrorCode INT,
	@LanguageID INT = NULL
)
RETURNS NVARCHAR(1024)
AS
BEGIN
	IF @LanguageID IS NULL
	BEGIN
		SELECT @LanguageID = [core].[fn_GetDefaultLanguageID]();
	END

	DECLARE	@errorText NVARCHAR(1024);
	SELECT @errorText = ErrorText FROM [core].[ErrorCodes]
		WHERE	ErrorCode = @ErrorCode
		AND		LanguageID = @LanguageID;
	RETURN @errorText;
END

GO

ALTER PROCEDURE [core].[SaveLogging]
	@LogDate DATETIME = GETDATE,
	@LogType INT,
	@VersionNumber VARCHAR(25),
	@CreatedBy NVARCHAR(256),
	@CreatedFromSource NVARCHAR(256),
	@ErrorCode INT = NULL,
	@LanguageID INT = NULL,
	@SessionID VARCHAR(50) = NULL
AS
BEGIN
	/* SSet the values in local variables */
	DECLARE
		@v_LogDate DATETIME = @LogDate,
		@v_LogType INT = @LogType,
		@v_VersionNumber VARCHAR(25) = @VersionNumber,
		@v_CreatedBy NVARCHAR(256) = @CreatedBy,
		@v_CreatedFromSource NVARCHAR(256) = @CreatedFromSource,
		@v_LanguageID INT = @LanguageID,
		@v_SessionID VARCHAR(50) = @SessionID,
		
		@v_currentVersionNumber VARCHAR(25)
	
	/* Error variables */
	DECLARE
		@v_errorCode INT = @ErrorCode,
		@v_errorText NVARCHAR(1024)
	
	/* if @LogDate is null, then set the current date timestamp */
	IF @v_LogDate IS NULL
	BEGIN
		SET @v_LogDate = GETDATE()
	END
	
	/* if LanguageID is null, then set it to default */
	IF @v_LanguageID IS NULL
	BEGIN
		SET @v_LanguageID = [core].[fn_GetDefaultLanguageID]() /* ??? from Settings-Table ??? */
	END
	
	/* if the logtype is null */
	IF @v_LogType IS NULL
	BEGIN
		SELECT @v_errorCode = error.ErrorCode, @v_errorText = error.ErrorText FROM core.fn_tbl_GetErrorCode('CORE_LOGGING_LOGTYPE_IS_NULL', @v_LanguageID) error
	END
	
	/* if the logtype is not between 0 and 3 */
	IF @v_LogType NOT BETWEEN 0 AND 3
	BEGIN
		SELECT @v_errorCode = error.ErrorCode, @v_errorText = error.ErrorText FROM core.fn_tbl_GetErrorCode('CORE_LOGGING_LOGTYPE_NOT_VALID', @v_LanguageID) error
	END
	
	/* If the given version not the current version */
	IF core.fn_IsCorrectVersion(@v_VersionNumber) = 0
	BEGIN
		SELECT @v_errorCode = error.ErrorCode, @v_errorText = error.ErrorText FROM core.fn_tbl_GetErrorCode('CORE_LOGGING_INCORRECT_VERSION', @v_LanguageID) error
		SELECT @v_currentVersionNumber = core.fn_GetCurrentVersion()
		
		INSERT INTO core.Logging(LogDate, LogType, VersionNumber, SessionID, ErrorCode, LogMessage, CreatedAt, CreatedBy, CreatedFromSource)
		SELECT @v_LogDate, 3, @v_currentVersionNumber, @v_SessionID, @v_errorCode, @v_errorText, GETDATE(), @v_CreatedBy, @v_CreatedFromSource
	END

	IF @ErrorCode IS NOT NULL-- AND @ErrorCode = @v_errorCode
	BEGIN
		SELECT @v_errorText = [core].[fn_GetErrorTextByErrorCode](@ErrorCode, @v_LanguageID);
	END

	INSERT INTO core.Logging(LogDate, LogType, VersionNumber, SessionID, ErrorCode, LogMessage, CreatedAt, CreatedBy, CreatedFromSource)
	SELECT @v_LogDate, @v_LogType, @v_VersionNumber, @v_SessionID, @v_errorCode, @v_errorText, GETDATE(), @v_CreatedBy, @v_CreatedFromSource
END
GO


ALTER PROCEDURE [finance].[AddBankkonto]
	@Kontonummer		NVARCHAR(50),
	@Bankleitzahl		NVARCHAR(50),
	@Bankname			NVARCHAR(256),
	@Kontoname			NVARCHAR(256),
	@IBAN				NVARCHAR(50) = NULL,
	@CreatedBy			NVARCHAR(256),
	@CreatedFromSource	NVARCHAR(256),
	@VersionNumber		VARCHAR(25),
	@SessionID			VARCHAR(50),
	@LanguageID			INT = NULL
AS
BEGIN
	DECLARE
		@CreatedAt DATETIME = GETDATE(),
		@errorReturn INT = 0,
		@errorText NVARCHAR(1024)

	IF @LanguageID IS NULL
	BEGIN
		SET @LanguageID = [core].[fn_GetDefaultLanguageID]();
	END

	-- Kontonummer prüfen
	EXEC @errorReturn = [core].[ValidateStringParameter] @Kontonummer, 0, 0, 0, 50, 'finance', 'Bankkonto', 1, @VersionNumber, @SessionID, @LanguageID;

	IF @errorReturn <> 0
	BEGIN
		EXEC [core].[SaveLogging] @CreatedAt, 3, @VersionNumber, @CreatedBy, @CreatedFromSource, 10001, @LanguageID, @SessionID;
		SELECT @errorText = core.fn_GetErrorTextByErrorCode(10001, @LanguageID);
		RAISERROR(@errorText, 16, 1);
		RETURN 10001;
	END

	-- Bankleitzahl prüfen
	EXEC @errorReturn = [core].[ValidateStringParameter] @Bankleitzahl, 0, 0, 0, 50, 'finance', 'Bankkonto', 1, @VersionNumber, @SessionID, @LanguageID;

	IF @errorReturn <> 0
	BEGIN
		EXEC [core].[SaveLogging] @CreatedAt, 3, @VersionNumber, @CreatedBy, @CreatedFromSource, 10002, @LanguageID, @SessionID;
		SELECT @errorText = core.fn_GetErrorTextByErrorCode(10002, @LanguageID);
		RAISERROR(@errorText, 16, 1);
		RETURN 10002;
	END

	-- Bankname prüfen
	EXEC @errorReturn = [core].[ValidateStringParameter] @Bankname, 0, 0, 0, 256, 'finance', 'Bankkonto', 1, @VersionNumber, @SessionID, @LanguageID;

	IF @errorReturn <> 0
	BEGIN
		EXEC [core].[SaveLogging] @CreatedAt, 3, @VersionNumber, @CreatedBy, @CreatedFromSource, 10003, @LanguageID, @SessionID;
		SELECT @errorText = core.fn_GetErrorTextByErrorCode(10003, @LanguageID);
		RAISERROR(@errorText, 16, 1);
		RETURN 10003;
	END

	-- Kontoname prüfen
	EXEC @errorReturn = [core].[ValidateStringParameter] @Bankname, 0, 0, 0, 256, 'finance', 'Bankkonto', 1, @VersionNumber, @SessionID, @LanguageID;

	IF @errorReturn <> 0
	BEGIN
		EXEC [core].[SaveLogging] @CreatedAt, 3, @VersionNumber, @CreatedBy, @CreatedFromSource, 10004, @LanguageID, @SessionID;
		SELECT @errorText = core.fn_GetErrorTextByErrorCode(10004, @LanguageID);
		RAISERROR(@errorText, 16, 1);
		RETURN 10004;
	END
	
	IF [finance].[fn_ExistsBankkontoByKontoname](@Kontoname) = 1
	BEGIN
		EXEC [core].[SaveLogging] @CreatedAt, 3, @VersionNumber, @CreatedBy, @CreatedFromSource, 10000, @LanguageID, @SessionID;
		SELECT @errorText = core.fn_GetErrorTextByErrorCode(10000, @LanguageID);
		RAISERROR(@errorText, 16, 1);
		RETURN 10000;
	END

	-- IBAN prüfen
	EXEC @errorReturn = [core].[ValidateStringParameter] @IBAN, 1, 1, 0, 50, 'finance', 'Bankkonto', 1, @VersionNumber, @SessionID, @LanguageID;

	IF @errorReturn <> 0
	BEGIN
		EXEC [core].[SaveLogging] @CreatedAt, 3, @VersionNumber, @CreatedBy, @CreatedFromSource, 10005, @LanguageID, @SessionID;
		SELECT @errorText = core.fn_GetErrorTextByErrorCode(10005, @LanguageID);
		RAISERROR(@errorText, 16, 1);
		RETURN 10005;
	END
	
	INSERT INTO [finance].[Bankkonto]
	(Kontonummer, Bankleitzahl, Bankname, Kontoname, IBAN, CreatedAt, CreatedBy, CreatedFromSource)
	SELECT @Kontonummer, @Bankleitzahl, @Bankname, @Kontoname, @IBAN, @CreatedAt, @CreatedBy, @CreatedFromSource

	RETURN 0;
END 